
(ns ken.pu.mycampus-helper
  ;(:require )
  (:use [clojure.contrib.sql :as sql]
        [clojure.contrib.str-utils]
        [ken.pu.lucene-helper])
  ;(:import )
  )


(defn try-connection
  []
  (let [db {:classname "org.sqlite.JDBC" 
            :subprotocol "sqlite" 
            :subname "mycampus_201109.sq3"}]
    (sql/with-connection db
      (sql/with-query-results rs ["SELECT * FROM courses where code like :1" "csci%"]
        (doseq [row rs]
          (println row))))))

(def *mycampus*
  {:classname "org.sqlite.JDBC"
   :subprotocol "sqlite"
   :subname "mycampus_201109.sq3"})

(defn join-values [row attrs]
  (str-join " " (for [[k v] row :when (some #{(name k)} attrs)] v)))

(defn crawl-entities [ent-def f]
  (sql/with-connection *mycampus*
    (sql/with-query-results results [(ent-def :sql)]
      (doseq [row results]
        (f ent-def row)))))

(def *course-entity*
  {:entity "course"
   :sql "SELECT code as id, code, title FROM courses"
   :text-attr ["code" "title"]})

(def *schedule-entity*
  {:entity "schedule"
   :sql "SELECT
         T.id as id, 
         date_start, date_end, hour_start, hour_end, min_start, min_end, 
         location, year, capacity, actual, campus
         FROM sections S JOIN schedules T ON S.id = T.section_id
        "
   :text-attr ["location" "campus"]})

(def *person-entity*
  {:entity "person"
   :sql "SELECT id, name
         FROM instructors"
   :text-attr ["name"]})


(def *course-schedules*
  {:relation "course-schedules"
   :sql "SELECT semester,
                course,
                actual,
                campus,
                registration_start, registration_end,
                S.id as section_id,
                date_start,
                date_end,
                schedtype,
                hour_star, min_start,
                hour_end, min_end,
                location,
                T.id as schedule_id
         FROM sections S JOIN schedules T ON S.id = T.section_id LIMIT 100"
   :hierarchy 
      [
       {:entity "course" :key ["course"] :label ["semester"]}
       {:entity "section" :key ["section_id"] :label ["actual" "campus" "registration_start" "registration_end"]}
       {:entity "schedule" :key ["schedule_id"] :label ["date_start" "date_end" "hour_start" "min_start" "hour_end" "min_end" "location"]}
      ]
  })